package gorm

import (
	"fmt"
)

func Query1() {
	db := GetDB()

	var u = User{}
	var users []User

	db.First(&u)
	fmt.Println(u)

	//主键字段为0值时，或者与查询的主键值相同时，才正确接收值
	u.ID = 0
	db.Take(&u)
	fmt.Println(u)

	u.ID = 0
	db.Last(&u)
	fmt.Println(u)

	u.ID = 0
	db.First(&u, 7)
	fmt.Println(u)

	db.Find(&users)
	fmt.Println(users)
}

func Query2() {
	var u User
	var users []User

	db := GetDB()
	db.Where("name=?", "linfangyi").First(&u)
	db.Where("age > ?", 18).Find(&users)

	fmt.Println(u)
	fmt.Println(users)
	u.ID = 0
	users = []User{}

	db.Where("name <> ?", "fanshuai").Find(&users)
	fmt.Println(users)
	db.Where("id in (?,?)", 1, 2).Find(&users)
	fmt.Println(users)

	db.Where("name = ? and age = ?", "linfangyi", 20).Find(&users)
	fmt.Println(users)

	//结构体查询时，只查询非0字段
	db.Where(&User{Name: "jinzhu", Age: 20}).First(&u)
	db.Where(map[string]interface{}{"name": "jinzhu", "age": 20}).Find(&users)
	db.Where([]int64{20, 21, 22}).Find(&users)

	//内联条件
	// 根据主键获取记录 (只适用于整形主键)
	db.First(&users, 23)
	// SELECT * FROM users WHERE id = 23 LIMIT 1;

	// Plain SQL
	db.Find(&users, "name = ?", "jinzhu")
	// SELECT * FROM users WHERE name = "jinzhu";

	db.Find(&users, "name <> ? AND age > ?", "jinzhu", 20)
	// SELECT * FROM users WHERE name <> "jinzhu" AND age > 20;

	// Struct  结构体查询时，只查询非0字段
	db.Find(&users, User{Age: 20})
	// SELECT * FROM users WHERE age = 20;

	// Map
	db.Find(&users, map[string]interface{}{"age": 20})
	// SELECT * FROM users WHERE age = 20;

	var count = 0
	db.Where("name = ?", "jinzhu").Or("name = ?", "jinzhu 2").Find(&users).Count(&count)
	//// SELECT * from USERS WHERE name = 'jinzhu' OR name = 'jinzhu 2'; (users)
	//// SELECT count(*) FROM users WHERE name = 'jinzhu' OR name = 'jinzhu 2'; (count)

	db.Model(&User{}).Where("name = ?", "jinzhu").Count(&count)
	//// SELECT count(*) FROM users WHERE name = 'jinzhu'; (count)

	db.Table("deleted_users").Count(&count)
	//// SELECT count(*) FROM deleted_users;

	db.Table("deleted_users").Select("count(distinct(name))").Count(&count)
	//// SELECT count( distinct(name) ) FROM deleted_users; (count)

	//扫描
	type Result struct {
		Name string
		Age  int
	}
	var result Result
	db.Table("user").Select("name, age").Where("name = ?", "Antonio").Scan(&result)

	var results []Result
	db.Table("user").Select("name, age").Where("id > ?", 0).Scan(&results)

	//原生 SQL
	db.Raw("SELECT name, age FROM users WHERE name = ?", "Antonio").Scan(&result)
}

func QueryPage() {
	var users []User
	db := GetDB()
	//排序
	db.Order("age desc, name").Find(&users)
	// SELECT * FROM users ORDER BY age desc, name;
	fmt.Println(users)

	// 多字段排序
	db.Order("age desc").Order("name").Find(&users)
	// SELECT * FROM users ORDER BY age desc, name;
	fmt.Println(users)

	//分页
	db.Offset(3).Limit(10).Find(&users)
	fmt.Println(users)
}
